﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

/// <summary>
/// Connection Manager handles all connections to the database defined in the config
/// </summary>
public class ConnectionMaster
{
    string connectionString = ConfigurationManager.AppSettings["DataConnectionString"];
    //string readConnectionString = ConfigurationManager.AppSettings["DataReadConnectionString"];
    public ConnectionMaster() { }
    public DataTable retrieveDataTable(string _sqlString, ArrayList _sqlparameters)
    {

        SqlConnection sqlCon = new SqlConnection(this.connectionString);
        sqlCon.Open();

        SqlCommand sqlCom = new SqlCommand(_sqlString, sqlCon);
        // add parameters
        foreach (SqlParameter parameter in _sqlparameters)
            sqlCom.Parameters.Add(parameter);

        SqlDataReader reader = sqlCom.ExecuteReader();

        DataTable ret = new DataTable();
        ret.Load(reader);

        sqlCom.Dispose();
        reader.Close();
        reader.Dispose();
        sqlCon.Close();
        sqlCon.Dispose();
        return ret;
    }
    public long executeCommand(string _sqlString, ArrayList _sqlparameters , bool retrieveID)
    {
        SqlConnection sqlCon = new SqlConnection(this.connectionString);
        sqlCon.Open();
        long result = 0;

        if (retrieveID == true)
        {
            _sqlString += "; SELECT SCOPE_IDENTITY();";
            SqlCommand sqlCom = new SqlCommand(_sqlString, sqlCon);
            // add parameters
            foreach (SqlParameter parameter in _sqlparameters)
                sqlCom.Parameters.Add(parameter);
            result =  Convert.ToInt64(sqlCom.ExecuteScalar());
        }
        else
        {
            SqlCommand sqlCom = new SqlCommand(_sqlString, sqlCon);
            // add parameters
            foreach (SqlParameter parameter in _sqlparameters)
                sqlCom.Parameters.Add(parameter);
            result = sqlCom.ExecuteNonQuery();
        }
        sqlCon.Close();
        return result;

    }
    public DataTable limitDataTable(DataTable _refTable, int _limit)
    {
        DataTable dt = new DataTable();
        for (int i = 0; i < _refTable.Columns.Count; i++)
            dt.Columns.Add(_refTable.Columns[i].ColumnName, _refTable.Columns[i].DataType);

        if (_refTable.Rows.Count < _limit)
            _limit = _refTable.Rows.Count;
        for (int i = 0; i < _limit; i++)
            dt.Rows.Add(_refTable.Rows[i].ItemArray);

        return dt;
    }
}